# -*- coding: utf-8 -*-
__author__ = 'ggcall'

import xlrd,sqlite3,datetime
__s_date = datetime.date(1899, 12, 31).toordinal()-1
def getdate(date):
    # print type(date)
    if isinstance(date, float):
        date = int(date)
    d = datetime.date.fromordinal(__s_date + date)
    return d.strftime("%Y-%m-%d")

def insertdaily(r):
    conn.execute('INSERT INTO daily(' + ','.join(r.keys()) + ') VALUES(' + ','.join(['?'] * len(r)) + ')',
        tuple(r.values()))
    # conn.commit()
    return conn.execute('select last_insert_rowid()').fetchone()[0]
def insertdailyempe(r):
    conn.execute('INSERT INTO daily_empe(' + ','.join(r.keys()) + ') VALUES(' + ','.join(['?'] * len(r)) + ')',
        tuple(r.values()))
    # conn.commit()
def findempe(e):
    return conn.execute('select empeid from empe where empename=?',(e,)).fetchone()[0]

conn = sqlite3.connect(r'ndtv-networking.sqlite')
conn.text_factory = str
xls=xlrd.open_workbook('daily.xls')
table=xls.sheet_by_index(0)
for r in xrange(1,table.nrows):
    c=table.row_values(r)

    dailydate=getdate(c[0])
    dailytime=c[1]
    dailycontent=c[2]
    dailytype=c[3]
    dailyempe=c[4]
    starttime=c[5]
    endtime=c[6]
    dailymemo=c[7]
    print dailydate,dailytime,dailycontent,dailytype,dailyempe
    d={}
    d['dailydate']=dailydate
    d['dailytime']=dailytime
    d['dailycontent']=dailycontent
    if dailytype==u'加班':
        d['dailytypeid']=1
    elif dailytype==u'请假':
        d['dailytypeid']=2
    d['dailymemo']=dailymemo
    dailyid= insertdaily(d)
    empe=dailyempe.split(',')
    for e in empe:
        if e:
            de={}
            print e
            de['empeid']=findempe(e)
            de['dailyid']=dailyid
            insertdailyempe(de)

conn.commit()

